-- @owner: Alice
-- @date: 2025-09-03
-- @testpoint: 确定性函数支持result_cache,oracle语法的函数，合理报错
@conn PrimaryDbAmode;
drop extension if exists gms_output;
create extension gms_output;
set enable_func_cache = 1;
select gms_output.enable();
set query_dop=1;
set max_parallel_workers_per_gather=0;
----set serveroutput on;
set behavior_compat_options='skip_insert_gs_source,select_into_return_null,proc_outparam_override';
drop table if exists test_cycle;
drop function if exists f_test_2;
drop function if exists test_cycle_func1(text);
drop table if exists t;
create table t(id int,object_type varchar(30));
insert into t values(1,'a');
insert into t values(2,'a');
insert into t values(3,'a');
insert into t values(4,'b');
insert into t values(15,'b');
insert into t values(6,'b');
select count(*) from t;
--case1 oracle创建失败
create or replace function f_test_2(a in out varchar2) RETURN NUMBER  RESULT_CACHE IS
V_CNT NUMBER;
BEGIN
raise notice 'NOTICE: excute function...';
select count(*) into V_CNT from t where object_type=a;
return 12;
END;
/
--case2 创建成功
create or replace function f_test_2(a  varchar2) RETURN NUMBER  RESULT_CACHE IS
V_CNT NUMBER;
BEGIN
raise notice 'NOTICE: excute function...';
select count(*) into V_CNT from t where object_type=a;
return 12;
END;
/
select f_test_2('as');
select f_test_2('as') from t;
--case3
create or replace function f_test_2(a in varchar2) RETURN NUMBER  IS
V_CNT NUMBER;
BEGIN
raise notice 'NOTICE: excute function...';
select count(*) into V_CNT from t where object_type=a;
return 12;
END;
/
select f_test_2('ass');
select f_test_2('ass') from t;
alter function f_test_2(varchar2) RESULT_CACHE;
select f_test_2('ass');
select f_test_2('ass') from t;
-- recycle cache
set enable_func_cache = 1;
 
drop table if exists test_cycle;
create table test_cycle(id int,col text);
insert into test_cycle values(generate_series(1,2550),generate_series(1,2550));
insert into test_cycle values(generate_series(2551,2561),null);
update test_cycle set col=null where id in
(28,36,55,93,128,136,155,193,234,249,257,291,332,351,389,397,426,438,445,483,520,543,585,622,641,687,699,724,747,759,
781,814,869,877,912,971,1030,1053,1095,1128,1136,1155,1193,1234,1249,1257,1291,1332,1351,1389,1397,1426,1438,1445,
1483,1520,1543,1585,1622,1641,1687,1699,1724,1747,1759,1781,1814,1869,1877,1912,1971,2024,2047,2059,2081,2122,2141,
2187,2199,2220,2243,2285,2326,2338,2345,2383,2432,2451,2489,2497,2534,2549);
create or replace  function test_cycle_func1(p text) returns text
as $$
begin
    return p;
end;
$$ language plpgsql stable result_cache;
/
explain (costs off, analyze, timing off) select test_cycle_func1(col) from test_cycle;
update test_cycle set col=28 where col is null;
create or replace function test_cycle_func2(p text) returns text
as $$
begin
    if p = '28' then
        return null;
    end if;
    return p;
end;
$$ language plpgsql stable result_cache;
/
explain (costs off, analyze, timing off) select test_cycle_func2(col) from test_cycle;
--出现次数
--case4
--case4-1
create or replace function func_smallint_keyword_2(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable not result_cache not result_cache;
/
create or replace function func_smallint_keyword_2(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql stable result_cache result_cache;
/
create or replace function func_smallint_keyword_2(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql not result_cache result_cache;
/
create or replace function func_smallint_keyword_2(p smallint) returns smallint as $$
begin
raise notice 'NOTICE: excute function...';
if p = 1 then
gms_output.put_line('param:'||p);
end if;
return p+1;
end;
$$ language plpgsql deterministic result_cache result_cache;
/
create or replace function func_add_sql26(a1 integer, a2 integer) RETURN integer immutable not result_cache result_cache IS
v_cnt integer;
BEGIN
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
create or replace function func_add_sql26(a1 integer, a2 integer) RETURN integer not result_cache result_cache immutable IS
v_cnt integer;
BEGIN
v_cnt:=a1+a2;
RETURN v_cnt;
end;
/
create or replace function func_increment_2(i integer) RETURNS integer result_cache AS $$
BEGIN
raise notice 'NOTICE: excute function...';
RETURN i + 1;
END;
$$ LANGUAGE plpgsql result_cache;
/

--teardown
drop table if exists test_cycle;
drop function if exists f_test_2;
drop function  if exists test_cycle_func1(text);
